﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using SouthernLand.Common.BOL.Photo;
using SouthernLand.Common.Untilities;

namespace SouthernLand.Common.DAL.Photos
{
    public class PhotoCategoryDAL : BaseDAL
    {
        private PhotoCategory ObjectIDataReader(IDataReader dr)
        {
            var obj = new PhotoCategory();
            obj.CateID = (dr["CateID"] is DBNull) ? int.MinValue : (int) dr["CateID"];
            obj.AppKey = (dr["AppKey"] is DBNull) ? string.Empty : (string) dr["AppKey"];
            obj.CateName = (dr["CateName"] is DBNull) ? string.Empty : (string) dr["CateName"];
            obj.CateDesc = (dr["CateDesc"] is DBNull) ? string.Empty : (string) dr["CateDesc"];
            obj.CateName_en = (dr["CateName_en"] is DBNull) ? string.Empty : (string) dr["CateName_en"];
            obj.CateDesc_en = (dr["CateDesc_en"] is DBNull) ? string.Empty : (string) dr["CateDesc_en"];
            obj.ImageUrl = (dr["ImageUrl"] is DBNull) ? string.Empty : (string) dr["ImageUrl"];
            obj.CateOrder = (dr["CateOrder"] is DBNull) ? int.MinValue : (int) dr["CateOrder"];
            obj.ParentCateID = (dr["ParentCateID"] is DBNull) ? int.MinValue : (int) dr["ParentCateID"];
            return obj;
        }

        // lay AppKey cua PhotoCategory
        public string GetAppKeyByCateID(int CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    string value = "";
                    var myCommand = new SqlCommand("Photo_PhotoCategory_GetAppKeyByCateID", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                    value = (string) myCommand.ExecuteScalar();
                    myConn.Close();
                    return value;
                }
                catch
                {
                    return "";
                }
            }
        }

        public bool CheckAppKey(string AppKey)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                bool isValid = false;
                var myCommand = new SqlCommand("Photo_PhotoCategory_CheckAppKey", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@AppKey", SqlDbType.VarChar).Value = AppKey;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    isValid = true;
                    dr.Close();
                    myConn.Close();
                }
                return isValid;
            }
        }

        // lay Albums 
        public List<PhotoCategory> GetAlbums()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var list = new List<PhotoCategory>();
                string sql = "select * From Photo_PhotoCategory ";
                sql += " Where ParentCateID = " + (int) EnumCategory.AlbumsPublic;

                // khi tao user roi phai bat cai nay len
                if (HttpContext.Current.User.Identity.Name != null && HttpContext.Current.User.Identity.Name != "")
                    sql += " Or ParentCateID = " + (int) EnumCategory.AlbumsPrivate;
                sql += " Order by CateOrder ASC, CateID Desc";
                var myCommand = new SqlCommand(sql, myConn);
                myCommand.CommandType = CommandType.Text;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ObjectIDataReader(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }
        }

        public List<PhotoCategory> GetCateByParentCateID(int ParentCateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var list = new List<PhotoCategory>();
                var myCommand = new SqlCommand("Photo_PhotoCategory_GetCateByParentCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ParentCateID", SqlDbType.Int).Value = ParentCateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        list.Add(ObjectIDataReader(dr));
                    }
                    dr.Close();
                    myConn.Close();
                }
                return list;
            }
        }

        public PhotoCategory GetCateByCateID(int CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var obj = new PhotoCategory();
                var myCommand = new SqlCommand("Photo_PhotoCategory_GetCateByCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                SqlDataReader dr = myCommand.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        obj = ObjectIDataReader(dr);
                    }
                    dr.Close();
                    myConn.Close();
                }
                return obj;
            }
        }

        // Lay MaLoai Tu dong 
        public int GetAutoNumberCateID()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    int value = 0;
                    var myCommand = new SqlCommand("Photo_PhotoCategory_GetMaxCateID", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;
                    value = Convert.ToInt32(myCommand.ExecuteScalar());
                    myConn.Close();
                    return (value + 1);
                }
                catch
                {
                    return 0;
                }
            }
        }

        public int Insert(PhotoCategory obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Photo_PhotoCategory_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = GetAutoNumberCateID();
                myCommand.Parameters.Add("@AppKey", SqlDbType.VarChar).Value = obj.AppKey;
                myCommand.Parameters.Add("@CateName", SqlDbType.NVarChar).Value = obj.CateName;
                myCommand.Parameters.Add("@CateDesc", SqlDbType.NVarChar).Value = obj.CateDesc;
                myCommand.Parameters.Add("@CateName_en", SqlDbType.NVarChar).Value = obj.CateName_en;
                myCommand.Parameters.Add("@CateDesc_en", SqlDbType.NVarChar).Value = obj.CateDesc_en;
                myCommand.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = obj.ImageUrl;
                myCommand.Parameters.Add("@CateOrder", SqlDbType.Int).Value = obj.CateOrder;
                myCommand.Parameters.Add("@ParentCateID", SqlDbType.Int).Value = obj.ParentCateID;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return GetAutoNumberCateID();
            }
        }

        public int Update(PhotoCategory obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Photo_PhotoCategory_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                myCommand.Parameters.Add("@AppKey", SqlDbType.VarChar).Value = obj.AppKey;
                myCommand.Parameters.Add("@CateName", SqlDbType.NVarChar).Value = obj.CateName;
                myCommand.Parameters.Add("@CateDesc", SqlDbType.NVarChar).Value = obj.CateDesc;
                myCommand.Parameters.Add("@CateName_en", SqlDbType.NVarChar).Value = obj.CateName_en;
                myCommand.Parameters.Add("@CateDesc_en", SqlDbType.NVarChar).Value = obj.CateDesc_en;
                myCommand.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = obj.ImageUrl;
                myCommand.Parameters.Add("@CateOrder", SqlDbType.Int).Value = obj.CateOrder;
                myCommand.Parameters.Add("@ParentCateID", SqlDbType.Int).Value = obj.ParentCateID;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return obj.CateID;
            }
        }

        public int Delete(int CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Photo_PhotoCategory_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                myCommand.ExecuteNonQuery();
                myConn.Close();
                return CateID;
            }
        }
       
    }
}